﻿using System;
using System.Collections.Generic;
using System.Data;

namespace AssettDatabase.DAL
{
    class Asset
    {
        private string sqltext = "";

        public long Add(int Customer_ID, string Serial_Number, string Model_Number, int Asset_Type_ID,
                        string Vendor_Name, DateTime A_S_Purchase_Date, DateTime Sold_To_Customer_Date, 
                        string A_S_Invoice_Number, int Supplier_ID, DateTime Software_Renewal_Date, DateTime Warranty_Expiry, 
                        string Supplier_Invoice_Number, string Asset_Notes, List<VO.Asset_Image> Asset_Images, int Asset_InStock)
        {
            long Asset_ID = -1; 
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext = "INSERT INTO Assets (Customer_ID, Serial_Number,  Model_Number, Asset_Type_ID, Vendor_Name, A_S_Purchase_Date," +
                            "Sold_To_Customer_Date,  A_S_Invoice_Number, Warranty_Expiry, Software_Renewal_Date, Supplier_ID, Supplier_Invoice_Number, Asset_Notes, Asset_Status, Asset_InStock)" +
                            "VALUES (@Customer_ID, @Serial_Number, @Model_Number, @Asset_Type_ID, @Vendor_Name, @A_S_Purchase_Date," +
                            "@Sold_To_Customer_Date, @A_S_Invoice_Number, @Warranty_Expiry, @Software_Renewal_Date, @Supplier_ID, @Supplier_Invoice_Number, @Asset_Notes, 1, @Asset_InStock);";

                mDAO.AddParameter("@Customer_ID", Customer_ID);
                mDAO.AddParameter("@Serial_Number", Serial_Number);
                mDAO.AddParameter("@Model_Number", Model_Number);
                mDAO.AddParameter("@Asset_Type_ID", Asset_Type_ID);
                mDAO.AddParameter("@Vendor_Name", Vendor_Name);
                mDAO.AddParameter("@A_S_Purchase_Date", A_S_Purchase_Date);
                mDAO.AddParameter("@Sold_To_Customer_Date", Sold_To_Customer_Date);
                mDAO.AddParameter("@A_S_Invoice_Number", A_S_Invoice_Number);
                mDAO.AddParameter("@Warranty_Expiry", Warranty_Expiry);
                mDAO.AddParameter("@Software_Renewal_Date", Software_Renewal_Date);
                mDAO.AddParameter("@Supplier_ID", Supplier_ID);
                mDAO.AddParameter("@Supplier_Invoice_Number", Supplier_Invoice_Number);
                mDAO.AddParameter("@Asset_Notes", Asset_Notes);
                mDAO.AddParameter("@Asset_InStock", Asset_InStock);

                Asset_ID = mDAO.ExecuteNonQueryWithID(sqltext);
               
                foreach (VO.Asset_Image Asset_Image in Asset_Images)
                {
                    mDAO = new DataAccess.DAO();
                    sqltext = "INSERT INTO Asset_Images (Asset_ID, FileName)" +
                               "VALUES (@Asset_ID, @FileName);";

                    mDAO.AddParameter("@Asset_ID", Asset_ID);
                    mDAO.AddParameter("@FileName", Asset_Image.FileName);
                    mDAO.ExecuteNonQuery(sqltext);
                }
            }
            catch (Exception ex)
            {
                Asset_ID = -1;
                throw ex;
            }

            return Asset_ID;
        }

        public bool Update(int Customer_ID, string Serial_Number, string Model_Number, int Asset_Type_ID,
                string Vendor_Name, DateTime A_S_Purchase_Date, DateTime Sold_To_Customer_Date,
                string A_S_Invoice_Number, int Supplier_ID, DateTime Software_Renewal_Date, DateTime Warranty_Expiry,
                string Supplier_Invoice_Number, string Asset_Notes, List<VO.Asset_Image> Asset_Images, int Asset_InStock,int Asset_ID)
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext = "UPDATE Assets SET Customer_ID = @Customer_ID," +
                            "Serial_Number = @Serial_Number," +
                            "Model_Number = @Model_Number," +
                            "Asset_Type_ID = @Asset_Type_ID," +
                            "Vendor_Name = @Vendor_Name," +
                            "A_S_Purchase_Date = @A_S_Purchase_Date," +
                            "Sold_To_Customer_Date = @Sold_To_Customer_Date," +
                            "A_S_Invoice_Number = @A_S_Invoice_Number," +
                            "Warranty_Expiry = @Warranty_Expiry," +
                            "Software_Renewal_Date = @Software_Renewal_Date," +
                            "Supplier_ID = @Supplier_ID," +
                            "Supplier_Invoice_Number = @Supplier_Invoice_Number," +
                            "Asset_Notes = @Asset_Notes," +
                            "Asset_InStock = @Asset_InStock " +
                            "WHERE Asset_ID=@Asset_ID;";

                mDAO.AddParameter("@Customer_ID", Customer_ID);
                mDAO.AddParameter("@Serial_Number", Serial_Number);
                mDAO.AddParameter("@Model_Number", Model_Number);
                mDAO.AddParameter("@Asset_Type_ID", Asset_Type_ID);
                mDAO.AddParameter("@Vendor_Name", Vendor_Name);
                mDAO.AddParameter("@A_S_Purchase_Date", A_S_Purchase_Date);
                mDAO.AddParameter("@Sold_To_Customer_Date", Sold_To_Customer_Date);
                mDAO.AddParameter("@A_S_Invoice_Number", A_S_Invoice_Number);
                mDAO.AddParameter("@Warranty_Expiry", Warranty_Expiry);
                mDAO.AddParameter("@Software_Renewal_Date", Software_Renewal_Date);
                mDAO.AddParameter("@Supplier_ID", Supplier_ID);
                mDAO.AddParameter("@Supplier_Invoice_Number", Supplier_Invoice_Number);
                mDAO.AddParameter("@Asset_Notes", Asset_Notes);
                mDAO.AddParameter("@Asset_InStock", Asset_InStock);
                mDAO.AddParameter("@Asset_ID", Asset_ID);

                mDAO.ExecuteNonQuery(sqltext);

                //drop old images 
                mDAO = new DataAccess.DAO();
                sqltext = "DELETE FROM Asset_Images WHERE Asset_ID=@Asset_ID;";

                mDAO.AddParameter("@Asset_ID", Asset_ID);
                mDAO.ExecuteNonQuery(sqltext);

                //insert images data
                foreach (VO.Asset_Image Asset_Image in Asset_Images)
                {
                    mDAO = new DataAccess.DAO();
                    sqltext = "INSERT INTO Asset_Images (Asset_ID, FileName)" +
                               "VALUES (@Asset_ID, @FileName);";

                    mDAO.AddParameter("@Asset_ID", Asset_ID);
                    mDAO.AddParameter("@FileName", Asset_Image.FileName);
                    mDAO.ExecuteNonQuery(sqltext);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return true;
        }

        public bool Del(int Asset_ID)
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext = "UPDATE Assets " +
                            "SET Asset_Status=0 " +
                            "WHERE Asset_ID=@Asset_ID";

                mDAO.AddParameter("@Asset_ID", Asset_ID);
                mDAO.ExecuteNonQuery(sqltext);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return true;
        }

        public bool Del_Asset_Image(int Asset_ID, string FileName)
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext = "DELETE FROM Asset_Images WHERE Asset_ID=@Asset_ID AND FileName=@FileName;";

                mDAO.AddParameter("@Asset_ID", Asset_ID);
                mDAO.AddParameter("@FileName", FileName);
                mDAO.ExecuteNonQuery(sqltext);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return true;
        }

        public DataTable GetAsset()
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext = "SELECT A.Asset_ID, A.Customer_ID, A.Serial_Number, A.Model_Number, A.Asset_Type_ID, A.Vendor_Name, A.A_S_Purchase_Date, A.Sold_To_Customer_Date, " +
                            "A.A_S_Invoice_Number, A.Warranty_Expiry, A.Software_Renewal_Date, A.Supplier_ID, A.Supplier_Invoice_Number, A.Asset_Notes, Asset_Status, Asset_InStock, " +
                            "C.Customer_Name, Atype.Asset_Type_Name, S.Supplier_Name " +
                            "FROM Assets A " +
                            "INNER JOIN Customer C ON C.Customer_ID = A.Customer_ID " +
                            "INNER JOIN Asset_Type Atype ON Atype.Asset_Type_ID = A.Asset_Type_ID " +
                            "INNER JOIN Suppliers S ON S.Supplier_ID = A.Supplier_ID " +
                            "WHERE Asset_Status=1;";

                return mDAO.ExecuteSelectQuery(sqltext);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetAssetById(int Asset_ID)
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext = "SELECT A.Asset_ID, A.Customer_ID, A.Serial_Number, A.Model_Number, A.Asset_Type_ID, A.Vendor_Name, A.A_S_Purchase_Date, A.Sold_To_Customer_Date, " +
                            "A.A_S_Invoice_Number, A.Warranty_Expiry, A.Software_Renewal_Date, A.Supplier_ID, A.Supplier_Invoice_Number, A.Asset_Notes, Asset_Status, Asset_InStock, " +
                            "C.Customer_Name, Atype.Asset_Type_Name, S.Supplier_Name " +
                            "FROM Assets A " +
                            "INNER JOIN Customer C ON C.Customer_ID = A.Customer_ID " +
                            "INNER JOIN Asset_Type Atype ON Atype.Asset_Type_ID = A.Asset_Type_ID " +
                            "INNER JOIN Suppliers S ON S.Supplier_ID = A.Supplier_ID " +
                            "WHERE Asset_ID=@Asset_ID;";

                mDAO.AddParameter("@Asset_ID", Asset_ID);
                return mDAO.ExecuteSelectQuery(sqltext);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetAsset_ImagesById(int Asset_ID)
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext = "SELECT Asset_ID, FileName, Image_ID " +
                            "FROM Asset_Images " +
                            "WHERE Asset_ID=@Asset_ID;";

                mDAO.AddParameter("@Asset_ID", Asset_ID);
                return mDAO.ExecuteSelectQuery(sqltext);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetAssetsData(int Customer_ID, string Serial_Number, string Model_Number, int Asset_Type_ID,
                                        string Vendor_Name, int Supplier_ID, DateTime Warranty_From, DateTime Warranty_To,
                                        bool Expired)
        {
            try
            {
                DataAccess.DAO mDAO = new DataAccess.DAO();
                sqltext = "SELECT * " +
                            "FROM Assets " +
                            "INNER JOIN Asset_Type ON Asset_Type.Asset_Type_ID = Assets.Asset_Type_ID " +
                            "INNER JOIN Customer ON Customer.Customer_ID = Assets.Customer_ID " +
                            "INNER JOIN Vendors ON Vendors.Vendor_Name = Assets.Vendor_Name " +
                            "INNER JOIN Suppliers ON Suppliers.Supplier_ID = Assets.Supplier_ID " +
                            "WHERE 	(Assets.Customer_ID = @Customer_ID OR @Customer_ID = -1) AND " +
                            "(Assets.Serial_Number = @Serial_Number OR @Serial_Number = '') AND " +
                            "(Assets.Model_Number = @Model_Number OR @Model_Number = '') AND " +
                            "(Assets.Asset_Type_ID = @Asset_Type_ID OR @Asset_Type_ID = -1) AND " +
                            "(Assets.Supplier_ID = @Supplier_ID OR @Supplier_ID = -1) AND " +
                            "(Assets.Vendor_Name = @Vendor_Name OR @Vendor_Name = '') AND " +
                            "((Assets.Warranty_Expiry BETWEEN DATE(@Warranty_From) AND DATE(@Warranty_To) AND @Expired=0) OR " +
                            "(Assets.Warranty_Expiry < now() AND @Expired=1));";

                mDAO.AddParameter("@Customer_ID", Customer_ID);
                mDAO.AddParameter("@Serial_Number", Serial_Number);
                mDAO.AddParameter("@Model_Number", Model_Number);
                mDAO.AddParameter("@Asset_Type_ID", Asset_Type_ID);
                mDAO.AddParameter("@Supplier_ID", Supplier_ID);
                mDAO.AddParameter("@Vendor_Name", Vendor_Name);
                mDAO.AddParameter("@Warranty_From", Warranty_From);
                mDAO.AddParameter("@Warranty_To", Warranty_To);
                mDAO.AddParameter("@Expired", Expired);
                return mDAO.ExecuteSelectQuery(sqltext);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
